//
//  DataBaseHandler.m
//  UI21_SQLite
//
//  Created by Scott on 16/1/11.
//  Copyright © 2016年 Scott. All rights reserved.
//

#import "DataBaseHandler.h"
#import <sqlite3.h>
#import "Student.h"

@implementation DataBaseHandler

#pragma mark - 知识点1 单例
+ (instancetype)shareDataBaseHandler {
    
    // 在静态区, 只初始化一次.
    static DataBaseHandler *dataBase = nil;
    
    if (dataBase == nil) {
        dataBase = [[DataBaseHandler alloc] init];
    }
    
    return dataBase;
}

// 定义一个sqlite结构体(对象)
sqlite3 *db;

#pragma mark - 知识点2: 打开数据库.
- (void)open {
    
    // API: sqlite3_open
    
    
    if (db != nil) {
        NSLog(@"数据库已经开启!!!");
        return;
    }
    
    NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"student.sqlite"];
    
    /** 
     * @param filename 数据库文件的路径(注意,类型为C字符串)
     * @param ppDb 数据库对象
     */
    int result = sqlite3_open(file.UTF8String, &db);
    
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功!");
        
    } else {
        NSLog(@"数据库打开失败, code = %d", result);
        
    }
    
}

#pragma mark - 知识点3: 创建表格.
- (void)createTable {
    
    // API: sqlite3_exec
    
    // 创建SQL语句.
    NSString *creatTableSQL = @"CREATE TABLE IF NOT EXISTS lanou1130(number INTEGER PRIMARY KEY AUTOINCREMENT, name text, sex text, age int)";
    
    int result = sqlite3_exec(db, creatTableSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表格成功!");
    } else {
        NSLog(@"创建表格失败. code:%d", result);
    }
}


#pragma mark - 知识点4: 插入数据.
- (void)insertStudent:(Student *)student {
    // API: sqlite3_exec
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT into lanou1130(name, sex, age) VALUES('%@', '%@', '%d')", student.name, student.sex, student.age];
    
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"添加数据完成!");
    } else {
        NSLog(@"添加数据失败.code:%d", result);
    }
    
}

#pragma mark - 知识点5: 更新数据.
- (void)updataStudent:(Student *)stu forNumber:(NSInteger)number {
    
    // API: sqlite3_exec
    
    NSString *updataSQL = [NSString stringWithFormat:@"UPDATE lanou1130 SET name = '%@', sex = '%@', age = '%d' WHERE number = '%ld'", stu.name, stu.sex, stu.age, number];
    
    int result = sqlite3_exec(db, updataSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    } else {
        NSLog(@"更新失败, code:%d", result);
    }
    
}


#pragma mark - 知识点6: 删除数据.
- (void)deleteWithNumber:(NSInteger)number {
    
    // API: sqlite3_exec
    
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM lanou1130 WHERE number = '%ld'", number];
    
    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功!");
    } else {
        NSLog(@"删除失败. code:%d", result);
    }
    
}


#pragma mark - 知识点7: 查询数据
- (NSArray *)selectForAge:(int)age {
    
    // 存放符合条件的数据(元素是Model对象)
    NSMutableArray *arr = [NSMutableArray array];
    
    // sql语句.
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130 WHERE age = '%d'", age];
    
    // API: stmt结构体, sqlite3_prepare_V2, sqlite3_step, sqlite3_column,
    
    // 声明一个stmt对象(结构体)
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"准备就绪, 查询中.....");
        
        // 每行都执行查询语句
        while (sqlite3_step(stmt) == SQLITE_ROW) {
        
            // 如果查询条件匹配. 通过sqlite3_column_函数簇 取出值.
            
            // 取出哪一列的数据值
            const unsigned char *name =  sqlite3_column_text(stmt, 1);
            
            const unsigned char *sex = sqlite3_column_text(stmt, 2);
            
            int age = sqlite3_column_int(stmt, 3);
            
            // 创建model对象, 赋值之后放入数组中.
            Student *stu = [[Student alloc] init];
            
            stu.name = [NSString stringWithUTF8String: (const char *)name];
            
            stu.sex = [NSString stringWithUTF8String:(const char *)sex];
            
            stu.age = age;
            
            [arr addObject:stu];

        }

        // 销毁对象.
        sqlite3_finalize(stmt);
    } else {
        
        NSLog(@"不能正常查询. code:%d", result);

        // 销毁对象.
        sqlite3_finalize(stmt);
    }
    
    
    
    return arr;
}

#pragma mark - 知识点8: 删除表单
- (void)dropTable {
    
    NSString *dropSQL = @"DROP TABLE lanou1130";
    
    int result = sqlite3_exec(db, dropSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"删除表格成功!!!");
    } else {
        
        NSLog(@"删除表格失败.code:%d", result);
    }
    
    
}

#pragma mark - 知识点9: 关掉数据库.
- (void)closeDB {
    
    // API: sqlite3_close
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
        db = nil;
    } else {
        NSLog(@"数据库关闭失败. code:%d", result);
    }
    
}















@end
